package com.codefactory.generator.utils;

import com.codefactory.generator.config.DataSourceConfig;
import com.codefactory.generator.model.TableColumn;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;

@Slf4j
@Component
public class DatabaseUtil {
    @Autowired
    private DataSourceConfig dataSourceConfig;

    /**
     * 查询表的字段列表信息
     *
     * @param tableName 表名
     * @return 字段信息列表
     * @throws Exception
     */
    public List<TableColumn> getColumnList(final String tableName) throws Exception {
        if (StringUtils.isBlank(tableName)) return Collections.emptyList();
        List<String> objects = new LinkedList<>();
        objects.add(tableName);
        //
        Map<String, List<TableColumn>> map = getColumnListForTables(objects);
        List<TableColumn> columnList = map.get(tableName);
        return columnList;
    }

    /**
     * 根据表集合或者表的字段信息Map
     *
     * @param tableList
     * @return
     * @throws Exception
     */
    public Map<String, List<TableColumn>> getColumnListForTables(List<String> tableList) throws Exception {
        if (tableList == null || tableList.isEmpty()) return Collections.emptyMap();
        Map<String, List<TableColumn>> tableColumnsMap = new LinkedHashMap<>();
        //与数据库的连接
        try (Connection conn = createConnection(); Statement statement = conn.createStatement()) {
            //
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            String productName = databaseMetaData.getDatabaseProductName();
            String productVersion = databaseMetaData.getDatabaseProductVersion();
            //log.info("\nproductName={},productVersion={}", productName, productVersion);

            final String commentSql;
            if ("MySQL".equals(productName)) {
                //tableCommentSql = "show full columns from " + tableName;
                final String fsql = "SELECT A.COLUMN_NAME ,A.COLUMN_COMMENT FROM information_schema.`COLUMNS` A\n" +//
                        "WHERE table_schema = (SELECT DATABASE()) AND A.TABLE_NAME='%s'\n" +//
                        "ORDER BY A.ORDINAL_POSITION ASC";
                commentSql = fsql;
            } else if ("Oracle".equals(productName) || "DM DBMS".equals(productName)) {
                final String fsql = "SELECT C.COLUMN_NAME ,C.COMMENTS COLUMN_COMMENT FROM USER_TAB_COMMENTS A\n" +//
                        "INNER JOIN USER_COL_COMMENTS C ON C.TABLE_NAME=A.TABLE_NAME\n" +//
                        "INNER JOIN USER_TAB_COLUMNS COL ON COL.TABLE_NAME=C.TABLE_NAME AND COL.COLUMN_NAME=C.COLUMN_NAME\n" +//
                        "WHERE A.TABLE_NAME='%s'\n" +//
                        "ORDER BY COL.COLUMN_ID";
                commentSql = fsql;
            } else {
                throw new Exception("没有对应数据库的查询字段SQL");
            }

            for (String tableName : tableList) {
                //
                String querySQL = String.format(commentSql, tableName);
                Map<String, String> commentMap = new LinkedHashMap<>();
                List<TableColumn> columnList = new ArrayList<>();
                //
                try (ResultSet rs = statement.executeQuery(querySQL)) {
                    while (rs.next()) {
                        String field = rs.getString("COLUMN_NAME");
                        String comment = rs.getString("COLUMN_COMMENT");
                        commentMap.put(field, comment == null ? "" : comment);
                    }
                }
                //
                try (PreparedStatement pStemt = conn.prepareStatement("SELECT * FROM " + tableName)) {
                    ResultSetMetaData rsmd = pStemt.getMetaData();
                    if (rsmd == null) throw new Exception(tableName + " :表不存在！");
                    //表列数
                    int size = rsmd.getColumnCount();
                    for (int ind = 1; ind <= size; ind++) {
                        String columnName = rsmd.getColumnName(ind);
                        String columnType = rsmd.getColumnTypeName(ind);
                        String className = rsmd.getColumnClassName(ind);
                        //
                        TableColumn tableColumn = new TableColumn();
                        tableColumn.setColumnName(columnName);
                        tableColumn.setColumnType(columnType);
                        tableColumn.setColumnClassName(className);
                        tableColumn.setColumnComment(commentMap.get(columnName));
                        columnList.add(tableColumn);
                    }
                    //
                    List<TableColumn> tableColumnList = TableColumnUtil.parseColumn(columnList);
                    tableColumnsMap.put(tableName, tableColumnList);
                } catch (Exception e) {
                    log.error("{} :表查询表字段异常:{}", tableName, e.getMessage(), e);
                    throw e;
                }
            }//tableList for end
        } catch (Exception e) {
            throw e;
        }
        return tableColumnsMap;
    }

    /**
     * 获取数据库元数据
     *
     * @return
     * @throws Exception
     */
    public DatabaseMetaData getDatabaseMetaData() throws Exception {
        DatabaseMetaData databaseMetaData = null;
        //与数据库的连接
        try (Connection conn = createConnection()) {
            //
            databaseMetaData = conn.getMetaData();
        }
        return databaseMetaData;
    }

    //创建数据库连接
    public Connection createConnection() throws Exception {
        //与数据库的连接
        Class.forName(dataSourceConfig.getDriverClassName());
        try {
            Connection conn = DriverManager.getConnection(dataSourceConfig.getUrl(), dataSourceConfig.getUsername(), dataSourceConfig.getPassword());
            return conn;
        } catch (Exception ex) {
            throw new Exception("创建数据库连接异常：" + dataSourceConfig, ex);
        }
    }

    /**
     * 查询表注释
     *
     * @param tableList
     * @return
     * @throws Exception
     */
    public Map<String, String> getTableCommentMap(List<String> tableList) throws Exception {
        //
        String tabStr = String.format("'%s'", StringUtils.join(tableList, "','"));
        Map<String, String> map = new LinkedHashMap<>();
        //与数据库的连接
        try (Connection conn = createConnection(); Statement stmt = conn.createStatement()) {
            //
            StringJoiner joiner = new StringJoiner("\n");
            DatabaseMetaData metaData = conn.getMetaData();
            String productName = metaData.getDatabaseProductName();
            if ("MySQL".equals(productName)) {
                joiner.add("SELECT table_name, table_comment FROM information_schema.TABLES WHERE table_schema = (SELECT DATABASE())");
                joiner.add(String.format("and table_name IN(%s)", tabStr));
            } else if ("Oracle".equals(productName) || "DM DBMS".equals(productName)) {
                joiner.add("SELECT A.COMMENTS AS table_comment ,A.TABLE_NAME FROM USER_TAB_COMMENTS A WHERE 1=1");
                joiner.add(String.format("and A.TABLE_NAME IN(%s)", tabStr));
            } else {
                throw new Exception("没有对应数据库的查询SQL");
            }
            String sql = joiner.toString();
            try (ResultSet rs = stmt.executeQuery(sql)) {
                while (rs.next()) {
                    String tableName = rs.getString("table_name");
                    String tableComment = rs.getString("table_comment");
                    map.put(tableName, tableComment);
                }
            }
        }
        return map;
    }
}
